# -*- coding: utf-8 -*-
"""
财产保险成本费用指标计算代码
工单编号：大数据-八维保险数据挖掘-05-财产保险成本费用相关
功能：计算财产保险业务的各项成本费用指标，支持按七日、月、年统计
"""

import pymysql
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import logging

# 配置日志
logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
                    filename='insurance_cost_indicator.log')
logger = logging.getLogger(__name__)


class InsuranceCostIndicator:
    """财产保险成本费用指标计算类"""

    def __init__(self, db_config):
        """初始化数据库连接"""
        self.db_config = db_config
        self.conn = None
        self.cursor = None
        self.connect_db()

    def connect_db(self):
        """连接数据库"""
        try:
            self.conn = pymysql.connect(
                host=self.db_config['host'],
                user=self.db_config['user'],
                password=self.db_config['password'],
                database=self.db_config['database'],
                port=self.db_config['port'],
                charset='utf8mb4'
            )
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
            logger.info("数据库连接成功")
        except Exception as e:
            logger.error(f"数据库连接失败: {str(e)}")
            raise

    def close_db(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
        logger.info("数据库连接已关闭")

    def get_period_data_with_join(self, period_type, date_str, region=None):
        """关联查询保费收入表和保单表，获取policy_type"""
        try:
            date = datetime.strptime(date_str, '%Y-%m-%d')

            # 确定日期范围
            if period_type == 'daily':
                start_date = date_str
                end_date = date_str
            elif period_type == 'weekly':
                weekday = date.weekday()
                start_date = (date - timedelta(days=weekday)).strftime('%Y-%m-%d')
                end_date = (date + timedelta(days=6 - weekday)).strftime('%Y-%m-%d')
            elif period_type == 'monthly':
                start_date = date.strftime('%Y-%m-01')
                end_date = (date.replace(day=1) + timedelta(days=32)).replace(day=1) - timedelta(days=1)
                end_date = end_date.strftime('%Y-%m-%d')
            elif period_type == 'yearly':
                start_date = date.strftime('%Y-01-01')
                end_date = date.strftime('%Y-12-31')
            else:
                raise ValueError("不支持的周期类型，支持'daily', 'weekly', 'monthly', 'yearly'")

            # 构建查询条件
            where_clause = f"pi.create_time BETWEEN '{start_date}' AND '{end_date} 23:59:59'"
            if region:
                where_clause += f" AND pi.region = '{region}'"

            # 关联查询
            sql = f"""
            SELECT pi.*, p.policy_type 
            FROM premium_income pi
            JOIN policy p ON pi.policy_id = p.policy_id
            WHERE {where_clause};
            """
            logger.info(f"执行关联查询: {sql}")
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            return pd.DataFrame(data)

        except Exception as e:
            logger.error(f"获取关联数据失败: {str(e)}")
            raise

    # 修改get_period_data方法，添加字段检查功能
    def get_period_data(self, table_name, period_type, date_str, region=None, required_fields=None):
        """
        获取指定周期的数据，并检查必要字段
        :param required_fields: 需要检查的字段列表，如['reserve_type', 'ending_balance']
        """
        try:
            date = datetime.strptime(date_str, '%Y-%m-%d')

            # 确定日期范围（保持原有逻辑）
            if period_type == 'daily':
                start_date = date_str
                end_date = date_str
            elif period_type == 'weekly':
                weekday = date.weekday()
                start_date = (date - timedelta(days=weekday)).strftime('%Y-%m-%d')
                end_date = (date + timedelta(days=6 - weekday)).strftime('%Y-%m-%d')
            elif period_type == 'monthly':
                start_date = date.strftime('%Y-%m-01')
                end_date = (date.replace(day=1) + timedelta(days=32)).replace(day=1) - timedelta(days=1)
                end_date = end_date.strftime('%Y-%m-%d')
            elif period_type == 'yearly':
                start_date = date.strftime('%Y-01-01')
                end_date = date.strftime('%Y-12-31')
            else:
                raise ValueError("不支持的周期类型，支持'daily', 'weekly', 'monthly', 'yearly'")

            # 构建查询条件
            where_clause = f"create_time BETWEEN '{start_date}' AND '{end_date} 23:59:59'"
            if region:
                where_clause += f" AND region = '{region}'"

            # 查询数据
            sql = f"SELECT * FROM {table_name} WHERE {where_clause};"
            logger.info(f"执行查询: {sql}")
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
            df = pd.DataFrame(data)

            # 检查必要字段是否存在
            if required_fields:
                missing_fields = [field for field in required_fields if field not in df.columns]
                if missing_fields:
                    error_msg = f"表{table_name}中缺少必要字段: {', '.join(missing_fields)}"
                    logger.error(error_msg)
                    # 抛出异常而非返回空值，确保问题被及时发现
                    raise ValueError(error_msg)

            logger.info(f"获取{period_type}数据成功，共{len(df)}条记录")
            return df

        except Exception as e:
            logger.error(f"获取{period_type}数据失败: {str(e)}")
            raise

    # 修改calculate_outstanding_claim_ratio方法，指定必要字段
    def calculate_outstanding_claim_ratio(self, period_type, date_str, region=None):
        """计算未决赔款准备金与赔款支出比"""
        try:
            logger.info(f"开始计算{period_type}未决赔款准备金与赔款支出比，日期: {date_str}, 区域: {region or '全部'}")

            # 定义准备金表必要字段
            reserve_required = ['reserve_type', 'ending_balance']
            # 获取本期准备金数据（指定必要字段检查）
            reserve_df = self.get_period_data(
                'reserve', period_type, date_str, region,
                required_fields=reserve_required
            )

            # 筛选未决赔款准备金（类型'01'）
            outstanding_reserve = reserve_df[reserve_df['reserve_type'] == '01']
            ending_reserve = outstanding_reserve['ending_balance'].sum()

            # 获取上期准备金数据（同样检查字段）
            prev_date = (datetime.strptime(date_str, '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
            prev_reserve_df = self.get_period_data(
                'reserve', period_type, prev_date, region,
                required_fields=reserve_required
            )

            # 筛选上期未决赔款准备金
            prev_outstanding_reserve = prev_reserve_df[prev_reserve_df['reserve_type'] == '01']
            beginning_reserve = prev_outstanding_reserve['ending_balance'].sum()

            # 获取赔付支出数据（检查amount字段）
            claim_df = self.get_period_data(
                'claim_payment', period_type, date_str, region,
                required_fields=['amount']
            )
            total_claim = claim_df['amount'].sum()

            # 获取摊回赔付支出（检查recovered_claim字段）
            reinsurance_df = self.get_period_data(
                'reinsurance', period_type, date_str, region,
                required_fields=['recovered_claim']
            )
            recovered_claim = reinsurance_df['recovered_claim'].sum()

            # 计算比率
            numerator = ending_reserve - beginning_reserve
            denominator = total_claim - recovered_claim

            if denominator == 0:
                logger.warning("未决赔款准备金与赔款支出比计算分母为0，返回0")
                return 0.0

            ratio = (numerator / denominator) * 100
            logger.info(f"{period_type}未决赔款准备金与赔款支出比计算完成: {ratio:.2f}%")
            return round(ratio, 2)

        except Exception as e:
            logger.error(f"未决赔款准备金与赔款支出比计算失败: {str(e)}")
            raise

    # 修改calculate_long_term_surrender_rate方法
    def calculate_long_term_surrender_rate(self, period_type, date_str, region=None):
        """计算长期险退保率"""
        try:
            logger.info(f"开始计算{period_type}长期险退保率，日期: {date_str}, 区域: {region or '全部'}")

            # 获取退保数据
            surrender_df = self.get_period_data('surrender', period_type, date_str, region)
            if surrender_df.empty:
                logger.warning("没有获取到退保数据，长期险退保率为0")
                return 0.0
            # 检查必要字段是否存在
            required_fields = ['surrender_amount', 'insurance_type']
            for field in required_fields:
                if field not in surrender_df.columns:
                    logger.error(f"退保表中缺少必要字段: {field}")
                    return 0.0
            # 计算报告期退保金总额
            total_surrender = surrender_df['surrender_amount'].sum()
            # 计算报告期短期险退保金
            short_term_surrender = surrender_df[surrender_df['insurance_type'] == '02']['surrender_amount'].sum()

            # 获取长期险责任准备金提转差
            reserve_df = self.get_period_data('reserve', period_type, date_str, region)

            # 检查准备金表是否包含必要字段
            if 'reserve_type' not in reserve_df.columns:
                logger.error("准备金表中缺少reserve_type字段")
                return 0.0
            if 'transfer_difference' not in reserve_df.columns:
                logger.error("准备金表中缺少transfer_difference字段")
                return 0.0

            long_term_reserve = reserve_df[reserve_df['reserve_type'].isin(['03', '04'])]
            transfer_difference = long_term_reserve['transfer_difference'].sum()

            # 计算期初长期险责任准备金
            prev_date = (datetime.strptime(date_str, '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
            prev_reserve_df = self.get_period_data('reserve', period_type, prev_date, region)

            # 检查上期准备金数据字段
            if 'reserve_type' not in prev_reserve_df.columns:
                logger.error("上期准备金表中缺少reserve_type字段")
                return 0.0
            if 'ending_balance' not in prev_reserve_df.columns:
                logger.error("上期准备金表中缺少ending_balance字段")
                return 0.0

            prev_long_term_reserve = prev_reserve_df[prev_reserve_df['reserve_type'].isin(['03', '04'])]
            beginning_reserve = prev_long_term_reserve['ending_balance'].sum()

            # 获取报告期长期险保费收入
            premium_df = self.get_period_data_with_join(period_type, date_str, region)

            # 检查保费数据字段
            if 'policy_type' not in premium_df.columns:
                logger.error("保费数据中缺少policy_type字段")
                return 0.0
            if 'amount' not in premium_df.columns:
                logger.error("保费数据中缺少amount字段")
                return 0.0

            long_term_premium = premium_df[premium_df['policy_type'] == '01']['amount'].sum()

            # 计算长期险退保率
            numerator = total_surrender - short_term_surrender + transfer_difference
            denominator = beginning_reserve + long_term_premium

            if denominator == 0:
                logger.warning("长期险退保率计算分母为0，返回0")
                return 0.0

            rate = (numerator / denominator) * 100
            logger.info(f"{period_type}长期险退保率计算完成: {rate:.2f}%")
            return round(rate, 2)

        except Exception as e:
            logger.error(f"长期险退保率计算失败: {str(e)}")
            raise

    def calculate_outstanding_claim_ratio(self, period_type, date_str, region=None):
        """计算未决赔款准备金与赔款支出比"""
        try:
            logger.info(f"开始计算{period_type}未决赔款准备金与赔款支出比，日期: {date_str}, 区域: {region or '全部'}")

            # 获取本期准备金数据
            reserve_df = self.get_period_data('reserve', period_type, date_str, region)
            outstanding_reserve = reserve_df[reserve_df['reserve_type'] == '01']
            ending_reserve = outstanding_reserve['ending_balance'].sum()

            # 获取上期准备金数据
            prev_date = (datetime.strptime(date_str, '%Y-%m-%d') - timedelta(days=1)).strftime('%Y-%m-%d')
            prev_reserve_df = self.get_period_data('reserve', period_type, prev_date, region)
            prev_outstanding_reserve = prev_reserve_df[prev_reserve_df['reserve_type'] == '01']
            beginning_reserve = prev_outstanding_reserve['ending_balance'].sum()

            # 获取赔付支出数据
            claim_df = self.get_period_data('claim_payment', period_type, date_str, region)
            total_claim = claim_df['amount'].sum()

            # 获取摊回赔付支出
            reinsurance_df = self.get_period_data('reinsurance', period_type, date_str, region)
            recovered_claim = reinsurance_df['recovered_claim'].sum()

            # 计算比率
            numerator = ending_reserve - beginning_reserve
            denominator = total_claim - recovered_claim

            if denominator == 0:
                logger.warning("未决赔款准备金与赔款支出比计算分母为0，返回0")
                return 0.0

            ratio = (numerator / denominator) * 100
            logger.info(f"{period_type}未决赔款准备金与赔款支出比计算完成: {ratio:.2f}%")
            return round(ratio, 2)

        except Exception as e:
            logger.error(f"未决赔款准备金与赔款支出比计算失败: {str(e)}")
            raise

    def calculate_paid_claim_ratio(self, period_type, date_str, region=None):
        """计算已付赔款赔付率 (业务年度)"""
        try:
            logger.info(f"开始计算{period_type}已付赔款赔付率，日期: {date_str}, 区域: {region or '全部'}")

            # 获取业务年度
            business_year = datetime.strptime(date_str, '%Y-%m-%d').strftime('%Y')

            # 获取业务年度已付赔款
            claim_df = self.get_period_data('claim_payment', period_type, date_str, region)
            current_year_claims = claim_df[claim_df['business_year'] == business_year]
            paid_claims = current_year_claims['amount'].sum()

            # 获取业务年度已赚保费
            premium_df = self.get_period_data_with_join(period_type, date_str, region)
            current_year_premiums = premium_df[premium_df['business_year'] == business_year]
            earned_premiums = current_year_premiums['earned_premium'].sum()

            # 计算已付赔款赔付率
            if earned_premiums == 0:
                logger.warning("已付赔款赔付率计算分母为0，返回0")
                return 0.0

            ratio = (paid_claims / earned_premiums) * 100
            logger.info(f"{period_type}已付赔款赔付率计算完成: {ratio:.2f}%")
            return round(ratio, 2)

        except Exception as e:
            logger.error(f"已付赔款赔付率计算失败: {str(e)}")
            raise

    def calculate_reported_claim_ratio(self, period_type, date_str, region=None):
        """计算已报告赔款赔付率 (业务年度)"""
        try:
            logger.info(f"开始计算{period_type}已报告赔款赔付率，日期: {date_str}, 区域: {region or '全部'}")

            # 获取业务年度
            business_year = datetime.strptime(date_str, '%Y-%m-%d').strftime('%Y')

            # 获取业务年度已决赔款
            claim_df = self.get_period_data('claim_payment', period_type, date_str, region)
            current_year_claims = claim_df[(claim_df['business_year'] == business_year) &
                                           (claim_df['status'] == '01')]  # 01表示已决
            decided_claims = current_year_claims['amount'].sum()

            # 获取已发生已报告未决赔款准备金（假设类型为'02'）
            reserve_df = self.get_period_data('reserve', period_type, date_str, region)
            reported_reserve = reserve_df[(reserve_df['reserve_type'] == '02') &
                                          (reserve_df['business_year'] == business_year)]
            reported_reserve_amount = reported_reserve['ending_balance'].sum()

            # 获取业务年度已赚保费
            premium_df = self.get_period_data_with_join(period_type, date_str, region)
            current_year_premiums = premium_df[premium_df['business_year'] == business_year]
            earned_premiums = current_year_premiums['earned_premium'].sum()

            # 计算已报告赔款赔付率
            if earned_premiums == 0:
                logger.warning("已报告赔款赔付率计算分母为0，返回0")
                return 0.0

            ratio = ((decided_claims + reported_reserve_amount) / earned_premiums) * 100
            logger.info(f"{period_type}已报告赔款赔付率计算完成: {ratio:.2f}%")
            return round(ratio, 2)

        except Exception as e:
            logger.error(f"已报告赔款赔付率计算失败: {str(e)}")
            raise

    def calculate_business_year_claim_ratio(self, period_type, date_str, region=None):
        """计算业务年度赔付率"""
        try:
            logger.info(f"开始计算{period_type}业务年度赔付率，日期: {date_str}, 区域: {region or '全部'}")

            # 获取业务年度
            business_year = datetime.strptime(date_str, '%Y-%m-%d').strftime('%Y')

            # 获取业务年度已决赔款
            claim_df = self.get_period_data('claim_payment', period_type, date_str, region)
            current_year_claims = claim_df[(claim_df['business_year'] == business_year) &
                                           (claim_df['status'] == '01')]
            decided_claims = current_year_claims['amount'].sum()

            # 获取已发生已报告未决赔款准备金（类型'02'）
            reserve_df = self.get_period_data('reserve', period_type, date_str, region)
            reported_reserve = reserve_df[(reserve_df['reserve_type'] == '02') &
                                          (reserve_df['business_year'] == business_year)]
            reported_reserve_amount = reported_reserve['ending_balance'].sum()

            # 获取已发生未报告未决赔款准备金（类型'03'）
            unreported_reserve = reserve_df[(reserve_df['reserve_type'] == '03') &
                                            (reserve_df['business_year'] == business_year)]
            unreported_reserve_amount = unreported_reserve['ending_balance'].sum()

            # 获取业务年度已赚保费
            premium_df = self.get_period_data_with_join(period_type, date_str, region)
            current_year_premiums = premium_df[premium_df['business_year'] == business_year]
            earned_premiums = current_year_premiums['earned_premium'].sum()

            # 计算业务年度赔付率
            if earned_premiums == 0:
                logger.warning("业务年度赔付率计算分母为0，返回0")
                return 0.0

            ratio = ((decided_claims + reported_reserve_amount + unreported_reserve_amount) / earned_premiums) * 100
            logger.info(f"{period_type}业务年度赔付率计算完成: {ratio:.2f}%")
            return round(ratio, 2)

        except Exception as e:
            logger.error(f"业务年度赔付率计算失败: {str(e)}")
            raise

    def calculate_comprehensive_capital_cost(self, period_type, date_str, region=None):
        """计算综合资本成本率"""
        try:
            logger.info(f"开始计算{period_type}综合资本成本率，日期: {date_str}, 区域: {region or '全部'}")

            # 获取资本数据
            capital_df = self.get_period_data('institution_capital', period_type, date_str, region)

            # 保险负债（类型'01'）
            insurance_liability = capital_df[capital_df['capital_type'] == '01']['ending_balance'].sum()
            # 运营资金（类型'02'）
            operating_funds = capital_df[capital_df['capital_type'] == '02']['ending_balance'].sum()
            # 所有者权益（类型'03'）
            owner_equity = capital_df[capital_df['capital_type'] == '03']['ending_balance'].sum()

            # 总资本
            total_capital = insurance_liability + operating_funds + owner_equity
            if total_capital == 0:
                logger.warning("总资本为0，综合资本成本率计算失败，返回0")
                return 0.0

            # 计算各部分占比
            insurance_ratio = insurance_liability / total_capital
            operating_ratio = operating_funds / total_capital
            equity_ratio = owner_equity / total_capital

            # 获取各部分成本率
            indicator_df = self.get_period_data('financial_indicators', period_type, date_str, region)

            # 保险负债成本率
            insurance_rate = indicator_df[indicator_df['indicator_type'] == 'insurance_liability_rate']['value'].sum() / \
                             max(1, len(indicator_df[indicator_df['indicator_type'] == 'insurance_liability_rate']))

            # 金融负债成本率
            financial_rate = indicator_df[indicator_df['indicator_type'] == 'financial_liability_rate']['value'].sum() / \
                             max(1, len(indicator_df[indicator_df['indicator_type'] == 'financial_liability_rate']))

            # 所有者权益成本率
            equity_rate = indicator_df[indicator_df['indicator_type'] == 'equity_cost_rate']['value'].sum() / \
                          max(1, len(indicator_df[indicator_df['indicator_type'] == 'equity_cost_rate']))

            # 计算综合资本成本率
            comprehensive_rate = (insurance_ratio * insurance_rate) + \
                                 (operating_ratio * financial_rate) + \
                                 (equity_ratio * equity_rate)

            logger.info(f"{period_type}综合资本成本率计算完成: {comprehensive_rate:.2f}%")
            return round(comprehensive_rate, 2)

        except Exception as e:
            logger.error(f"综合资本成本率计算失败: {str(e)}")
            raise

    def calculate_all_indicators(self, period_type, date_str, region=None):
        """计算所有指标并返回结果"""
        try:
            logger.info(f"开始计算{period_type}所有成本费用指标，日期: {date_str}, 区域: {region or '全部'}")

            result = {
                'period_type': period_type,
                'date': date_str,
                'region': region or '全部',
                'calculate_time': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                'indicators': {
                    'long_term_surrender_rate': self.calculate_long_term_surrender_rate(period_type, date_str, region),
                    'outstanding_claim_ratio': self.calculate_outstanding_claim_ratio(period_type, date_str, region),
                    'paid_claim_ratio': self.calculate_paid_claim_ratio(period_type, date_str, region),
                    'reported_claim_ratio': self.calculate_reported_claim_ratio(period_type, date_str, region),
                    'business_year_claim_ratio': self.calculate_business_year_claim_ratio(period_type, date_str,
                                                                                          region),
                    'comprehensive_capital_cost': self.calculate_comprehensive_capital_cost(period_type, date_str,
                                                                                            region)
                }
            }

            logger.info(f"{period_type}所有成本费用指标计算完成")
            return result

        except Exception as e:
            logger.error(f"所有指标计算失败: {str(e)}")
            raise


# 示例用法
if __name__ == "__main__":
    # 数据库配置（请替换为实际配置）
    db_config = {
        'host': 'localhost',
        'user': 'root',
        'password': 'root',  # 替换为实际密码
        'database': 'insurance_04',  # 替换为实际数据库名
        'port': 3306
    }

    # 创建指标计算实例
    indicator_calculator = InsuranceCostIndicator(db_config)

    try:
        # 计算七日（weekly）指标
        weekly_date = datetime.now().strftime('%Y-%m-%d')
        weekly_result = indicator_calculator.calculate_all_indicators('weekly', weekly_date, 'R001')
        print(f"七日指标计算结果: {weekly_result}")

        # 计算月度指标
        monthly_result = indicator_calculator.calculate_all_indicators('monthly', weekly_date, 'R001')
        print(f"月度指标计算结果: {monthly_result}")

        # 计算年度指标
        yearly_result = indicator_calculator.calculate_all_indicators('yearly', weekly_date, 'R001')
        print(f"年度指标计算结果: {yearly_result}")

    finally:
        # 关闭数据库连接
        indicator_calculator.close_db()